#!/usr/bin/env python3
# -*- coding:utf-8 -*-

import requests
import send_to_mail
import yyrc
from bs4 import BeautifulSoup
import pymysql
import logging
import time

logging.basicConfig(level=logging.INFO, \
                    format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s', \
                    datefmt='%a, %d %b %Y %H:%M:%S', \
                    filename='yuyao.log', \
                    filemode='a')

# url_yuyao = 'http://hr.eyuyao.com/search/job/?k=外贸&d=180&t=00&page=%d'
url_yuyao = 'https://hr.eyuyao.com/index/posts/index.html?keywords=外贸&sj=1&page=%d'


def parser_html(html):
    soup = BeautifulSoup(html, 'lxml')
    title = soup.find_all('strong')
    company = soup.find_all('div', class_='enterprise_name')
    date = soup.find_all('div', class_='date')
    pages = soup.find('div', class_='page')
    p = pages.find_all('a')

    t_list = [x.text for x in title][::2]

    c_list = []
    for i in company:
        c_list.append(i.a.contents[0])

    d_list = []
    for i in date:
        d_list.append(i.text)

    data = yyrc.fold_list(t_list, c_list, d_list)
    return data[len(data) - 20:], int(p[-2].text)


class yuyaodb:
    def __init__(self):
        self.db = pymysql.connect('localhost', 'spider', 'spider_password', 'spiderdb', charset='utf8')
        self.cursor = self.db.cursor()
        self.str_time = time.strftime("%Y%m%d")
        self.cur_time = time.strftime("%H:%M")

    def check_repeat(self, list):
        sql = 'SELECT id FROM yuyao WHERE title = %s AND company = %s AND date = %s;'

        try:
            self.cursor.execute(sql % (list[0], list[1], list[2]))
            result = self.cursor.fetchall()
        except pymysql.err.InternalError as e:
            logging.warning('[error]  %s' % e)
            result = None

        if result:
            for i in result:
                id = i[0]

                sql_update = "UPDATE yuyao SET is_new = 0 where id = %d;"
                self.cursor.execute(sql_update % id)
                self.db.commit()

            return True
        else:
            return False

    def exist_company(self, data):
        sql = "SELECT company FROM yuyao where company = %s and date = %s;"

        company = []
        try:
            self.cursor.execute(sql % (data, self.str_time))
            result = self.cursor.fetchall()
            for i in result:
                company.append(i)

        except:
            pass

        return len(company) != 0

    def insert(self, data):
        for i in data:
            index = 0
            if i[-1] != '昨天':
                i[-1] = self.str_time
            while index < 3:
                __tmp = "'" + i[index] + "'"
                i[index] = __tmp
                index += 1

            if self.check_repeat(i):
                continue

            if self.exist_company(i[1]):
                sql = 'INSERT INTO yuyao VALUE (NULL, %s, %s, %s, 0, %s);'
            else:
                sql = 'INSERT INTO yuyao VALUES(NULL, %s, %s, %s, 1, %s);'

            try:
                tmp = "'" + self.cur_time + "'"
                self.cursor.execute(sql % (i[0], i[1], i[2], tmp))
                self.db.commit()
            except:
                self.db.rollback()
                logging.warning('insert to database yuyao failed')

    def update_company(self):
        query_sql = "SELECT * FROM yuyao WHERE date = %s;"
        update_sql = "UPDATE yuyao SET is_new = 0 where id = %d;"

        try:
            tmp = "'" + self.str_time + "'"
            self.cursor.execute(query_sql % tmp)
            result = self.cursor.fetchall()
            for i in result:
                if i[-1] != self.cur_time:
                    try:
                        self.cursor.execute(update_sql % i[0])
                        self.db.commit()
                    except:
                        self.db.rollback()
        except:
            pass

    def query(self):

        table = []
        sql = "SELECT id, title, company, date, is_new FROM yuyao WHERE date = %s ORDER BY is_new DESC;"

        try:
            tmp = "'" + self.str_time + "'"
            self.cursor.execute(sql % tmp)
            result = self.cursor.fetchall()
            for i in result:
                table.append(list(i))
        except:
            logging.warning('[error] query data failed')

        return table

    def __del__(self):
        self.db.close()


if __name__ == '__main__':
    db = yuyaodb()

    h = requests.get(url_yuyao % 1)
    _, page = parser_html(h.text)
    for i in range(1, page + 1):
        #  html = next_page_u(url_yuyao, i)
        html = requests.get(url_yuyao % i)
        if html is None:
            print("can not get the pages")
            break

        data, _ = parser_html(html.text)
        if not data:
            break

        if data[0][-1] == '昨天':
            break

        db.insert(data)
        i += 1
        time.sleep(2)

    db.update_company()

    table_data = db.query()

    flag = False
    for i in table_data:
        if i[-1] == 1:
            flag = True

    table_str = send_to_mail.make_table(['职位名称', '公司名称', '日期', '新条目'], table_data)
    if flag:
        send_to_mail.send_email('haokabu@sina.com', table_str, '余姚生活网')
        logging.info('send email sccuss')
    else:
        logging.info('not sent email, program exit -> 0')
